﻿/***********************************************************************************************************************************
 *    Name: Set_Compatibility.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Set the Compatibility Level of all databases to the server version.
**/
DECLARE @Compatibility_Level NVARCHAR(5), @tsql NVARCHAR(MAX)= N'';

SELECT @Compatibility_Level= d.compatibility_level FROM sys.databases d WHERE d.database_id = 1;
SELECT @tsql+= N'ALTER DATABASE [' + name + N'] SET COMPATIBILITY_LEVEL= ' + @Compatibility_Level + N', PAGE_VERIFY CHECKSUM;
' FROM sys.databases WHERE 4 < database_id;
EXECUTE (@tsql);
GO